﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using ExcelExportLib;

namespace ExcelExportTest
{
    public partial class ExcelExportTestForm : Form
    {
        public ExcelExportTestForm()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ExcelDocument document = new ExcelDocument();

            //------------------------ Document Properties demo --------------------------
            document.Company = "ExcelExportLib";
            document.Author = "ExcelExportLib";
            document.LastAuthor = "ExcelExportLib";
            document.Description = "Description of" + Environment.NewLine + "this demo spreadsheet";
            document.Title = "ExcelExportLib demo spreadsheet";

            //----------------------------------------------------------------------------

            ExcelTable table = document.AddTable("Table 1");
            ExcelCell ec;

            table.AddColumn(false, 200);
            ExcelCell _HyperlinkTargetCellOtherTable;
            ExcelCell _HyperlinkTargetCellThisTable;
            ExcelRow row = table.AddRow();
            _HyperlinkTargetCellOtherTable = row.AddCell("Date only");
            row.AddCell(eDateType.Date, DateTime.Now);

            row = table.AddRow();
            row.AddCell("Date and time");
            row.AddCell(eDateType.DateTime, DateTime.Now);

            row = table.AddRow();
            row.AddCell("Time only");
            row.AddCell(eDateType.Time, DateTime.Now);

            row = table.AddRow();
            row.AddCell("Number");
            row.AddCell(15);

            row = table.AddRow();
            row.AddCell("Percent");
            row.AddCell(15, true);



            row = table.AddRow();
            row.AddCell("Date with custom style");
            ExcelCellStyle style = ExcelCellStyle.AddCellStyle("Bold_Date", "Date");
            style.Font.Italic = true;
            style.Font.Bold = true;
            style.horizontal = eHorizontalAlignment.Right;
            row.AddCell(eDateType.Date, DateTime.Now).cellStyle = "Bold_Date";

            row = table.AddRow();
            row.AddCell("Date with custom number format");
            style = ExcelCellStyle.AddCellStyle("Custom_Date", "Date");
            style.numberFormat=@"yyyy\,\ mmmm;@";
            /*if you want date or any number in one of Office's formats - just create empty worksheet in Excel
              set format for cell, save document and copy value from brackets. Something like:
            
              <Style ss:ID="s74">
                <NumberFormat ss:Format="yyyy\,\ mmmm;@"/>
              </Style>
              
             And you need to copy: "yyyy\,\ mmmm;@"
             */
            style.horizontal = eHorizontalAlignment.Right;
            row.AddCell(eDateType.Date, DateTime.Now).cellStyle = "Custom_Date";

            //------------------------ Borders demo --------------------------
            row = table.AddRow();
            row.SkipCell();

            ExcelCellStyle style_borders = ExcelCellStyle.AddCellStyle("Style_Borders");
            style_borders.AddBorder(eBorderPosition.Top, eBorderStyle.Continuous, eBorderWeight.Hairline, Color.Black);
            style_borders.AddBorder(eBorderPosition.Left, eBorderStyle.Dash, eBorderWeight.Medium, Color.Red);
            style_borders.AddBorder(eBorderPosition.Right, eBorderStyle.DashDot, eBorderWeight.Thick, Color.Green);
            style_borders.AddBorder(eBorderPosition.Bottom, eBorderStyle.DashDotDot, eBorderWeight.Thin, Color.Blue);

            row.AddCell("Border test").cellStyle = "Style_Borders";

            //------------------------ Background style demo --------------------------
            row = table.AddRow();
            row.SkipCell();
            ExcelCellStyle style_background = ExcelCellStyle.AddCellStyle("Style_Background");
            style_background.BacgroundColor = Color.Yellow;
            style_background.BackgroundPattern = eBackgroundPattern.Gray75;
            style_background.BackgroundPatternColor = Color.Ivory;
            row.AddCell("Background test").cellStyle = "Style_Background";

            //------------------------ Child style demo --------------------------
            ExcelCellStyle child_style = ExcelCellStyle.AddCellStyle("Child_Style", "Style_Background");
            child_style.Font.Italic = true;
            child_style.Font.Bold = true;
            row.AddCell("Child style test").cellStyle = "Child_Style";

            if (checkBox1.Checked)
            {
                ExcelCellStyle custom_font = ExcelCellStyle.AddCellStyle("Custom_Font");
                FontDialog _FontDialog = new FontDialog();
                if (_FontDialog.ShowDialog() == DialogResult.OK)
                {
                    custom_font.Font = new ExcelCellStyleFont(_FontDialog.Font);
                    custom_font.wrapText = false;
                    row = table.AddRow();
                    row.AddCell(string.Format("Custom font test: {0}, {1}", _FontDialog.Font.Name, _FontDialog.Font.Size)).cellStyle = "Custom_Font";
                }
            }

            //------------------------ Named cell demo ------------------------
            //warning: no duplicated names checked. Excel doesn't support duplicated names for cells
            table.SkipRow();
            row = table.AddRow();
            row.AddCell("Cell with custom name").cellName="Named_cell_1";
            row = table.AddRow();
            row.AddCell("Second cell with custom name").cellName = "Named_cell_2";

            //------------------------ formula cell demo ------------------------
            table.SkipRow();
            row = table.AddRow();
            row.AddCell("Formula test");
            ec = row.AddCell("=2+2");
            ec.type = eCellType.Formula;
            ec.cellName = "formula_test_cell";
            row.AddCell("=60 * formula_test_cell").type = eCellType.Formula; //formula with named cell
            row.AddCell("=SUM(R14C2:R14C3)").type = eCellType.Formula;       //formula with hard cell position ie $A$2
            row.AddCell("=SUM(RC[-3]:RC[-1])").type = eCellType.Formula;     //formula with position calculated from position of the current cell

            //------------------------ comments demo --------------------------
            table.SkipRow();
            row = table.AddRow();
            ec=row.AddCell("Commented cell");
            ec.comment = new ExcelCellComment("Comment's first line");
            ec.comment.AddLine("Comment's second line");

            row = table.AddRow();
            ec = row.AddCell("Commented cell with formatting");
            ec.comment = new ExcelCellComment();
            ec.comment.AddLine("Bold ");
            ec.comment.lines[0].commentLineElements[0].Bold = true;
            ec.comment.lines[0].AddElement(" or ");
            ec.comment.lines[0].AddElement("Italic", false, true);
            ec.comment.lines[0].AddElement(" or ");
            ec.comment.lines[0].AddElement("Underline", false, false, true);
            ec.comment.AddLine("Bold and Italic and Underline");
            ec.comment.lines[1].commentLineElements[0].Bold = true;
            ec.comment.lines[1].commentLineElements[0].Italic = true;
            ec.comment.lines[1].commentLineElements[0].Underline = true;


            //------------------------ second table demo ----------------------

            //second table in document
            table = document.AddTable("Table 2");
            table.AddColumn(false, 200);
            _HyperlinkTargetCellThisTable = table.AddRow().AddCell("second table test");


            //------------------------ Cell position demo --------------------------
            table.SkipRow(5);
            row = table.AddRow();
            row.AddCell("Cell position test:");
            row = table.AddRow();
            for (int i = 0; i < 5; i++)
            {
                ExcelCell cell = row.AddCell(string.Empty);
                cell.text = cell.GetPosition().ToString();
            }

            //------------------------ Hyperlinks demo --------------------------
            ExcelCellStyle style_hyperlink = ExcelCellStyle.AddCellStyle("Style_HyperLink");
            style_hyperlink.Font.Color = Color.Blue;
            style_hyperlink.excelStyleName = "Hyperlink";
            style_hyperlink.Font.Underline = eFontUnderline.SingleAccounting;

            row = table.AddRow();
            row.SkipCell(2);
            row.AddCell("HyperLink", "http://www.codeplex.com/ExcelExportLib").cellStyle = "Style_HyperLink";
            row = table.AddRow();
            row.SkipCell(2);
            row.AddCell(string.Format("{0}", _HyperlinkTargetCellThisTable.GetPosition()), table, _HyperlinkTargetCellThisTable).cellStyle = "Style_HyperLink"; ;

            row = table.AddRow();
            row.SkipCell(2);
            row.AddCell(string.Format("{0}, {1}", document.tables[0].tableName, _HyperlinkTargetCellThisTable.GetPosition()), document.tables[0], _HyperlinkTargetCellOtherTable).cellStyle = "Style_HyperLink"; ;

            //------------------------ Insert cell at position demo --------------
            ec = new ExcelCell("insert cell at position test");
            table.AddRow(new ExcelCellPosition(14, 3), ec);

            //------------------------ Removing unused styles demo ----------------
            //warning: don't use if you have inherited styles
            ExcelCellStyle style_to_remove = ExcelCellStyle.AddCellStyle("StyleToRemove");
            style_to_remove.Font.Color = Color.Blue;
            style_to_remove.Font.Underline = eFontUnderline.SingleAccounting;
            if (checkBox2.Checked)
            {
                document.CheckStylesBeforeSaving = false ;
            }


            //------------------------ Page splitting demo ----------------
            table = document.AddTable("Table 3");
            table.AddColumn(false, 250);
            row=table.AddRow();
            row.AddCell("fixed cell #1");
            row.AddCell("fixed cell #2");
            table.worksheetOptions = new WorksheetOptions();
            table.worksheetOptions.SplitHorizontal = 1;
            table.worksheetOptions.SplitVertical = 2;

            //saving and running excel
            document.Save("test.xml");
            RunExcel1("test.xml");
        }

        private static void RunExcel1(string filename)
        {
            try
            {
                System.Diagnostics.Process.Start("Excel", string.Concat("\"", filename, "\""));
            }
            catch (Exception ex)
            {
                RunExcel(filename);
            }
        }

        private static void RunExcel(string filename)
        {
            ProcessStartInfo startInfo = new ProcessStartInfo(filename);
            startInfo.UseShellExecute = true;
            Process.Start(startInfo);
        }
    }
}
